﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.Services;
using System.Data.OracleClient;
using System.Data;
using Netcare.Core;


namespace Netcare.Web.Services
{
    /// <summary>
    /// Summary description for oracle
    /// </summary>
    [WebService(Namespace = "http://www.wicresoft.com/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]

    public class Oracle : System.Web.Services.WebService
    {
        /// <summary>
        /// 检测回滚段的争用(Detection rollback contention)
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <returns>【返回卷段被访问的次数(平均值)、进程等待回卷段中某个进程的次数(平均值)】 DrcRV</returns>
        [WebMethod]
        public DrcRV GetDRCValues(string connectionString)
        {
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //GETS代表回卷段被访问的次数
                //WAITS代表进程等待回卷段中哪个进程的次数
                cmd.CommandText = "select sum(gets),sum(waits),sum(waits)/sum(gets) from v$rollstat";
                using (var odr = cmd.ExecuteReader())
                {
                    if (odr.Read())//读取数据，如果odr.Read()返回为false的话，就说明到记录集的尾部了
                    {
                        return new DrcRV()
                        {
                            DrcNVA = Convert.ToInt32(odr[0]),
                            DrcPNA = Convert.ToInt32(odr[1])
                        };
                    }
                    else
                    {
                        throw new Exception("检测回滚段的争用数据读取失败，可能系统繁忙，请稍候再试...");
                    }
                }
            }
        }
        /// <summary>
        /// GetDRCValues(检测回滚段的争用)的返回值
        /// </summary>
        [Serializable]
        public class DrcRV
        {
            //返回卷段被访问的次数(平均值)
            public int DrcNVA { get; set; }
            //进程等待回卷段中某个进程的次数(平均值) 
            public int DrcPNA { get; set; }
        }

        /// <summary>
        ///  监控事例的等待(Monitoring case of waiting)
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <returns>【等待事件、之前等待(平均值)、当前等待(平均值)、总数】 MWait[]</returns>
        [WebMethod]
        public MWait[] GetMWait(string connectionString)
        {
            List<MWait> list = new List<MWait>();
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //event=等待的事件 Prev=之前等待 Curr=当前等待 Tot=总数
                cmd.CommandText = "select event,sum(decode(wait_Time,0,0,1)) Prev, sum(decode(wait_Time,0,1,0)) Curr,count(*) Tot from v$session_Wait group by event order by 4";
                using (var odr = cmd.ExecuteReader())
                {
                    while (odr.Read())
                    {
                        MWait mw = mw = new MWait()
                        {
                            WaitThing = Convert.ToString(odr[0]),
                            PrevWait = Convert.ToInt32(odr[1]),
                            CurrWait = Convert.ToInt32(odr[2]),
                            Tot = Convert.ToInt32(odr[3])
                        };
                        list.Add(mw);
                    }
                    if (list.Count == 0)
                    {
                        throw new Exception("无法得到事例的等待，可能系统繁忙，请稍候再试...");
                    }
                    else
                    {
                        return list.ToArray();
                    }
                }
            }
        }

        /// <summary>
        /// GetMWait的返回值
        /// </summary>
        [Serializable]
        public class MWait
        {
            //等待的事件
            public string WaitThing { get; set; }
            //之前等待(平均值)
            public int PrevWait { get; set; }
            //当前等待(平均值)
            public int CurrWait { get; set; }
            //总数
            public int Tot { get; set; }
        }

        /// <summary>
        ///  监控表空间的 I/O 比例(Monitoring table space I/O ratio)
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <returns>【表空间名、文件名、已完成的物理读次数、块读取数、DBWR完成的物理写次数、写入磁盘的块数】TSIO[] </returns>
        [WebMethod]
        public TSIO[] GetTSIO(string connectionString)
        {
            List<TSIO> list = new List<TSIO>();
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //names = 表空间名 files = 文件名 pyr = 已完成的物理读次数 pbr = 块读取数 pyw = =DBWR完成的物理写次数 pbw = 写入磁盘的块数
                cmd.CommandText = "select df.tablespace_name names,df.file_name files,f.phyrds pyr, f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw from v$filestat f, dba_data_files df where f.file# = df.file_id order by df.tablespace_name";
                using (var odr = cmd.ExecuteReader())
                {
                    while (odr.Read())
                    {
                        TSIO tsio = new TSIO()
                         {
                             TsNames = Convert.ToString(odr[0]),
                             FFiles = Convert.ToString(odr[1]),
                             Pyr = Convert.ToInt32(odr[2]),
                             Pbr = Convert.ToInt32(odr[3]),
                             Pyw = Convert.ToInt32(odr[4]),
                             Pbw = Convert.ToInt32(odr[5])
                         };
                        list.Add(tsio);
                    }
                    if (list.Count == 0)
                    {
                        throw new Exception("无法得到表空间的I/O比例，可能系统繁忙，请稍候再试...");
                    }
                    else
                    {
                        return list.ToArray();
                    }
                }
            }
        }

        /// <summary>
        /// GetIO(表空间的I/O比例)的返回值
        /// </summary>
        [Serializable]
        public class TSIO
        {
            //表空间名
            public string TsNames { get; set; }
            //文件名
            public string FFiles { get; set; }
            //已完成的物理读次数
            public int Pyr { get; set; }
            //块读取数
            public int Pbr { get; set; }
            //DBWR完成的物理写次数
            public int Pyw { get; set; }
            //写入磁盘的块数
            public int Pbw { get; set; }
        }

        /// <summary>
        ///  监控文件系统的I/O比例(Monitor file system I/O ratio) 
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <returns>【文件标识号、数据文件的名称、状态、数据文件大小、已完成的物理读次数、DBWR完成的物理写次数】FSIO[] </returns>
        [WebMethod]
        public FSIO[] GetFSIO(string connectionString)
        {
            List<FSIO> list = new List<FSIO>();
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //FileLogo = 文件标识号 Names = 数据文件的名称 status = 状态 bytes = 数据文件大小 phyrds = 已完成的物理读次数 phywrts = DBWR完成的物理写次数
                cmd.CommandText = "select substr(a.file#,1,2) FileLogo, substr(a.name,1,30) Names, a.status, a.bytes, b.phyrds, b.phywrts from v$datafile a, v$filestat b where a.file# = b.file#";
                using (var odr = cmd.ExecuteReader())
                {
                    while (odr.Read())
                    {
                        FSIO fsio = new FSIO()
                        {
                            FileLogo = Convert.ToInt32(odr[0]),
                            Names = Convert.ToString(odr[1]),
                            Status = Convert.ToString(odr[2]),
                            Bytes = Convert.ToInt32(odr[3]),
                            Phyrds = Convert.ToInt32(odr[4]),
                            Phywrts = Convert.ToInt32(odr[5])
                        };
                        list.Add(fsio);
                    }
                    if (list.Count == 0)
                    {
                        throw new Exception("无法得到文件系统的I/O比例，可能系统繁忙，请稍候再试...");
                    }
                    else
                    {
                        return list.ToArray();
                    }
                }
            }
        }

        /// <summary>
        /// GetFSIO(文件系统的I/O比例)的返回值
        /// </summary>
        [Serializable]
        public class FSIO
        {
            //文件标识号
            public int FileLogo { get; set; }
            //数据文件的名称
            public string Names { get; set; }
            //状态
            public string Status { get; set; }
            //数据文件大小
            public int Bytes { get; set; }
            //已完成的物理读次数
            public int Phyrds { get; set; }
            //DBWR完成的物理写次数
            public int Phywrts { get; set; }

        }

        /// <summary>
        /// 监控SGA的命中率(SGA's hit rate monitor) 
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <returns>【统计值(逻辑读取)、已完成的物理读次数读取、缓冲区命中率】SGAHit[] </returns>
        [WebMethod]
        public SGAHit[] GetSGAHit(string connectionString)
        {
            List<SGAHit> list = new List<SGAHit>();
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //logical_reads = 统计值(逻辑读取)  phys_reads = 已完成的物理读次数读取 BUFFER HIT RATIO = 缓冲区命中率
                cmd.CommandText = "select a.value + b.value logical_reads, c.value phys_reads, round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) BUFFER_HIT_RATIO from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40";
                using (var odr = cmd.ExecuteReader())
                {
                    while (odr.Read())
                    {
                        SGAHit sga = new SGAHit()
                        {
                            LogicalReads = Convert.ToInt32(odr[0]),
                            PhysReads = Convert.ToInt32(odr[1]),
                            BHR = Convert.ToInt32(odr[2])
                        };
                        list.Add(sga);
                    }
                    if (list.Count == 0)
                    {
                        throw new Exception("无法得到SGA的命中率，可能系统繁忙，请稍候再试...");
                    }
                    else
                    {
                        return list.ToArray();
                    }
                }
            }
        }

        /// <summary>
        /// GetSGAHit(SGA的命中率)的返回值
        /// </summary>
        [Serializable]
        public class SGAHit
        {
            //统计值(逻辑读取)
            public int LogicalReads { get; set; }
            // 已完成的物理读次数读取
            public int PhysReads { get; set; }
            //缓冲区命中率
            public int BHR { get; set; }
        }

        /// <summary>
        /// 监控内存和硬盘的排序比(Monitor memory and hard disk sort ratio)
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <returns>【排序比名称、监控项的排序比】MemoryAndHardDiskSort[] </returns>
        [WebMethod]
        public MemoryAndHardDiskSort[] GetMemoryAndHardDiskSort(string connectionString)
        {
            List<MemoryAndHardDiskSort> list = new List<MemoryAndHardDiskSort>();
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //name = 排序比名称 value = 监控项的排序比
                cmd.CommandText = "SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)')";
                using (var odr = cmd.ExecuteReader())
                {
                    while (odr.Read())
                    {
                        MemoryAndHardDiskSort mahd = new MemoryAndHardDiskSort()
                        {
                            Name = Convert.ToString(odr[0]),
                            Value = Convert.ToInt32(odr[1])
                        };
                        list.Add(mahd);
                    }
                    if (list.Count == 0)
                    {
                        throw new Exception("无法得到内存和硬盘的排序比，可能系统繁忙，请稍候再试...");
                    }
                    else
                    {
                        return list.ToArray();
                    }
                }
            }
        }

        /// <summary>
        /// GetMemoryAndHardDiskSort(内存和硬盘的排序比)的返回值
        /// </summary>
        [Serializable]
        public class MemoryAndHardDiskSort
        {
            //监控项名称
            public string Name { get; set; }
            //监控项的排序比 
            public int Value { get; set; }
        }

        /// <summary>
        /// 监控当前数据库谁在运行什么SQL语句(Monitoring what the current database to run SQL statements who)
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <returns>【客户端操作系统用户名、当前用户、SQL文本】 SqlUserInfo[] </returns>
        [WebMethod]
        public SqlUserInfo[] GetSqlUserInfo(string connectionString)
        {
            List<SqlUserInfo> list = new List<SqlUserInfo>();
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //osuser = 客户端操作系统用户名 username = 当前用户 sql_text = SQL文本
                cmd.CommandText = "select osuser,username,sql_text from v$session a,v$sqltext b where a.sql_address = b.address order by address,piece";
                using (var odr = cmd.ExecuteReader())
                {
                    while (odr.Read())
                    {
                        SqlUserInfo sui = new SqlUserInfo()
                        {
                            OsUser = Convert.ToString(odr[0]),
                            UserName = Convert.ToString(odr[1]),
                            SqlText = Convert.ToString(odr[2])
                        };
                        list.Add(sui);
                    }
                    if (list.Count == 0)
                    {
                        throw new Exception("无法得到数据库谁在运行什么SQL语句，可能系统繁忙，请稍候再试...");
                    }
                    else
                    {
                        return list.ToArray();
                    }
                }
            }
        }

        /// <summary>
        /// GetSqlUserInfo(当前数据库谁在运行什么SQL语句)的返回值
        /// </summary>
        [Serializable]
        public class SqlUserInfo
        {
            //客户端操作系统用户名
            public string OsUser { get; set; }
            //当前用户
            public string UserName { get; set; }
            //SQL文本
            public string SqlText { get; set; }
        }

        /// <summary>
        /// 返回Oracle的版本信息(return to Oracle version information)
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <returns>【版本信息】 OracleVersion[] </returns>
        [WebMethod]
        public OracleVersion[] GetOracleVersion(string connectionString)
        {
            List<OracleVersion> list = new List<OracleVersion>();
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //版本信息
                cmd.CommandText = "select * from v$version";
                using (var odr = cmd.ExecuteReader())
                {
                    while (odr.Read())
                    {
                        OracleVersion ov = new OracleVersion()
                        {
                            Banner = Convert.ToString(odr[0])
                        };
                        list.Add(ov);
                    }
                    if (list.Count == 0)
                    {
                        throw new Exception("无法得到Oracle的版本信息，可能系统繁忙，请稍候再试...");
                    }
                    else
                    {
                        return list.ToArray();
                    }
                }
            }
        }

        /// <summary>
        /// GetOracleVersion(Oracle的版本信息)的返回值
        /// </summary>
        [Serializable]
        public class OracleVersion
        {
            //版本信息
            public string Banner { get; set; }
        }

        /// <summary>
        /// 监控SGA中字典缓冲区的命中率 (Monitoring SGA in the dictionary buffer hit ratio)
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <returns>【缓存名、请求总数、请求失败数、错过比率、命中率】 SGADictionary </returns>
        [WebMethod]
        public SGADictionary GetSGADictionary(string connectionString)
        {
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //parameter = 缓存名、 gets = 请求总数、 Getmisses = 请求失败数、 miss_ratio = 错过比率、 Hit_ratio = 命中率
                cmd.CommandText = "select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 miss_ratio, (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 Hit_ratio from v$rowcache where gets+getmisses <>0 group by parameter, gets, Getmisses, getmisses/(gets+getmisses)*100";
                using (var odr = cmd.ExecuteReader())
                {
                    if (odr.Read())
                    {
                        return new SGADictionary()
                        {
                            Parameter = Convert.ToString(odr[0]),
                            Gets = Convert.ToInt32(odr[1]),
                            Getmisses = Convert.ToInt32(odr[2]),
                            MissRatio = Convert.ToDouble(odr[3]),
                            HitRatio = Convert.ToDouble(odr[4])
                        };
                    }
                    else
                    {
                        throw new Exception("无法得到SGA中字典缓冲区的命中率，可能系统繁忙，请稍候再试...");
                    }
                }
            }
        }

        /// <summary>
        /// GetSGADictionary(SGA中字典缓冲区的命中率)的返回值
        /// </summary>
        [Serializable]
        public class SGADictionary
        {
            //缓存名
            public string Parameter { get; set; }
            //请求总数
            public int Gets { get; set; }
            //请求失败数
            public int Getmisses { get; set; }
            //错过比率
            public double MissRatio { get; set; }
            //命中率
            public double HitRatio { get; set; }
        }

        /// <summary>
        /// 监控SGA中共享缓存区的命中率 (Monitoring SGA shared buffer hit ratio)
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <returns>【共享池的命中率、百分比】 SGASBHR </returns>
        [WebMethod]
        public SGASBHR GetSGASBHR(string connectionString)
        {
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //hit radio = 共享池的命中率、reload percent = 百分比
                cmd.CommandText = "select sum(pinhits-reloads)/sum(pins) hit_radio,sum(reloads)/sum(pins) reload_percent from v$librarycache";
                using (var odr = cmd.ExecuteReader())
                {
                    if (odr.Read())
                    {
                        return new SGASBHR()
                        {
                            HitRadio = Convert.ToDouble(odr[0]),
                            ReloadPercent = Convert.ToDouble(odr[1])
                        };
                    }
                    else
                    {
                        throw new Exception("无法得到SGA中共享缓存区的命中率，可能系统繁忙，请稍候再试...");
                    }
                }
            }
        }

        /// <summary>
        /// GetSGASBHR(监控SGA中共享缓存区的命中率)的返回值
        /// </summary>
        [Serializable]
        public class SGASBHR
        {
            //共享池的命中率
            public double HitRadio { get; set; }
            //百分比
            public double ReloadPercent { get; set; }
        }

        /// <summary>
        /// 显示所有数据库对象的类别和大小(Show all types and size of the database objects)
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <returns>【实例的数量、类型、源的大小、解析大小、代码大小、错误大小、尺寸要求】 ShowAllTS[] </returns>
        [WebMethod]
        public ShowAllTS[] GetShowAllTS(string connectionString)
        {
            List<ShowAllTS> list = new List<ShowAllTS>();
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //num_instances = 实例的数量 type = 类型 source_size = 源的大小 parsed_size = 解析大小 code_size = 代码大小 error_size = 错误大小 size_required = 尺寸要求
                cmd.CommandText = "select count(name) num_instances ,type ,sum(source_size) source_size , sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size, sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required from dba_object_size group by type order by 2";
                using (var odr = cmd.ExecuteReader())
                {
                    while (odr.Read())
                    {
                        ShowAllTS sa = new ShowAllTS()
                        {
                            NumInstances = Convert.ToInt32(odr[0]),
                            Type = Convert.ToString(odr[1]),
                            SourceSize = Convert.ToInt32(odr[2]),
                            ParsedSize = Convert.ToInt32(odr[3]),
                            CodeSize = Convert.ToInt32(odr[4]),
                            ErrorSize = Convert.ToInt32(odr[5]),
                            SizeRequired = Convert.ToInt32(odr[6])
                        };
                        list.Add(sa);
                    }
                    if (list.Count == 0)
                    {
                        throw new Exception("无法得到所有数据库对象的类别和大小，可能系统繁忙，请稍后再试...");
                    }
                    else
                    {
                        return list.ToArray();
                    }
                }
            }
        }

        /// <summary>
        /// GetShowAllTS(显示所有数据库对象的类别和大小)的返回值
        /// </summary>
        [Serializable]
        public class ShowAllTS
        {
            //实例的数量
            public int NumInstances { get; set; }
            //类型
            public string Type { get; set; }
            //源的大小
            public int SourceSize { get; set; }
            //解析大小
            public int ParsedSize { get; set; }
            //代码大小
            public int CodeSize { get; set; }
            //错误大小
            public int ErrorSize { get; set; }
            //尺寸要求
            public int SizeRequired { get; set; }
        }

        /// <summary>
        /// 找ORACLE字符集(Find ORACLE character set)
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <returns>【名称、值、说明】 OracleCharacterSet </returns>
        [WebMethod]
        public OracleCharacterSet GetOracleCharacterSet(string connectionString)
        {
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //ORACLE字符集信息
                cmd.CommandText = "select * from sys.props$ where name='NLS_CHARACTERSET'";
                using (var odr = cmd.ExecuteReader())
                {
                    if (odr.Read())
                    {
                        return new OracleCharacterSet()
                        {
                            Names = Convert.ToString(odr[0]),
                            Values = Convert.ToString(odr[1])
                        };
                    }
                    else
                    {
                        throw new Exception("无法得到ORACLE字符集，可能系统繁忙，请稍后再试...");
                    }
                }
            }
        }

        /// <summary>
        /// GetOracleCharacterSet(找ORACLE字符集)的返回值
        /// </summary>
        [Serializable]
        public class OracleCharacterSet
        {
            //名称
            public string Names { get; set; }
            //值
            public string Values { get; set; }
        }

        /// <summary>
        /// 监控SGA中重做日志缓存区的命中率(Monitor redo log buffer in SGA hit rate)
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <returns>【name, gets, misses, immediate_gets, immediate_misses ratio1 ratio2】 SGALog[] </returns>
        [WebMethod]
        public SGALog[] GetSGALog(string connectionString)
        {
            List<SGALog> list = new List<SGALog>();
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //name, gets, misses, immediate_gets, immediate_misses ratio1 ratio2
                cmd.CommandText = "select name, gets, misses, immediate_gets, immediate_misses, Decode(gets,0,0,misses/gets*100) ratio1, Decode(immediate_gets+immediate_misses,0,0, immediate_misses/(immediate_gets+immediate_misses)*100) ratio2 from v$latch where name IN ('redo allocation', 'redo copy')";
                using (var odr = cmd.ExecuteReader())
                {
                    while (odr.Read())
                    {
                        SGALog sgal = new SGALog()
                        {
                            Name = Convert.ToString(odr[0]),
                            Gets = Convert.ToInt32(odr[1]),
                            Misses = Convert.ToInt32(odr[2]),
                            ImmediateGets = Convert.ToInt32(odr[3]),
                            ImmediateMisses = Convert.ToInt32(odr[4]),
                            Ratio1 = Convert.ToDouble(odr[5]),
                            Ratio2 = Convert.ToDouble(odr[6])
                        };
                        list.Add(sgal);
                    }
                    if (list.Count != 0)
                    {
                        return list.ToArray();
                    }
                    else
                    {
                        throw new Exception("无法得到SGA中重做日志缓存区的命中率，可能系统繁忙，请稍后再试...");
                    }
                }
            }
        }

        /// <summary>
        /// GetSGALog(监控SGA中重做日志缓存区的命中率)的返回值
        /// </summary>
        [Serializable]
        public class SGALog
        {
            public string Name { get; set; }
            public int Gets { get; set; }
            public int Misses { get; set; }
            public int ImmediateGets { get; set; }
            public int ImmediateMisses { get; set; }
            public double Ratio1 { get; set; }
            public double Ratio2 { get; set; }
        }

        /// <summary>
        /// 在某个用户下找所有的索引 (In a user to find all of the index under)
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <returns>【表名、索引名、uniqueness、列名】 UserIndex[] </returns>
        [WebMethod]
        public UserIndex[] GetUserIndex(string connectionString)
        {
            List<UserIndex> list = new List<UserIndex>();
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //table_name = 表名 index_name = 索引名 uniqueness column_name = 列名
                cmd.CommandText = "select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name from user_ind_columns, user_indexes where user_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name order by user_indexes.table_type, user_indexes.table_name, user_indexes.index_name, column_position";
                using (var odr = cmd.ExecuteReader())
                {
                    while (odr.Read())
                    {
                        UserIndex ui = new UserIndex()
                        {
                            TableName = Convert.ToString(odr[0]),
                            IndexName = Convert.ToString(odr[1]),
                            Qniqueness = Convert.ToString(odr[2]),
                            ColumnName = Convert.ToString(odr[3])
                        };
                        list.Add(ui);
                    }
                    if (list.Count != 0)
                    {
                        return list.ToArray();
                    }
                    else
                    {
                        throw new Exception("无法得到用户下找所有的索引，可能系统繁忙，请稍后再试...");
                    }
                }
            }
        }

        /// <summary>
        /// GetUserIndex(在某个用户下找所有的索引)的返回值
        /// </summary>
        [Serializable]
        public class UserIndex
        {
            //表名
            public string TableName { get; set; }
            //索引名
            public string IndexName { get; set; }
            public string Qniqueness { get; set; }
            //列名
            public string ColumnName { get; set; }
        }


        /// <summary>
        /// 找使用CPU多的用户session(CPU and more users looking to use session)
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <returns>【sid、spid、status、prog、terminal、osuser、value】 UserUseCPU[] </returns>
        [WebMethod]
        public UserUseCPU[] GetUserUseCPU(string connectionString)
        {
            List<UserUseCPU> list = new List<UserUseCPU>();
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //sid、 spid、 status = 状态、 prog、 terminal = 终端、  osuser = 当前用户、 value = 值
                cmd.CommandText = "select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value from v$session a,v$process b,v$sesstat c where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc";
                using (var odr = cmd.ExecuteReader())
                {
                    while (odr.Read())
                    {
                        UserUseCPU uuc = new UserUseCPU()
                        {
                            Sid = Convert.ToInt32(odr[0]),
                            Spid = Convert.ToInt32(odr[1]),
                            Status = Convert.ToString(odr[2]),
                            Prog = Convert.ToString(odr[3]),
                            Terminal = Convert.ToString(odr[4]),
                            Osuser = Convert.ToString(odr[5]),
                            Value = Convert.ToInt32(odr[6]),
                        };
                        list.Add(uuc);
                    }
                    if (list.Count != 0)
                    {
                        return list.ToArray();
                    }
                    else
                    {
                        throw new Exception("无法得到使用CPU多的用户session，可能系统繁忙，请稍后再试...");
                    }
                }
            }
        }

        /// <summary>
        /// GetUserUseCPU(找使用CPU多的用户session)的返回值
        /// </summary>
        [Serializable]
        public class UserUseCPU
        {
            //sid
            public int Sid { get; set; }
            //spid
            public int Spid { get; set; }
            //状态
            public string Status { get; set; }
            public string Prog { get; set; }
            //终端
            public string Terminal { get; set; }
            //当前用户
            public string Osuser { get; set; }
            //值
            public int Value { get; set; }
        }


        /// <summary>
        /// 监控字典缓冲区(Dictionary buffer control)
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <returns>【 Lib缓存、高速缓存行、处决、缓存未命中、同时执行】 DBuffer </returns>
        [WebMethod]
        public DBuffer GetDBuffer(string connectionString)
        {
            DBuffer dbf = new DBuffer();
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //Lib缓存
                cmd.CommandText = "SELECT (SUM(PINS - RELOADS)) / SUM(PINS) LIB_CACHE FROM V$LIBRARYCACHE";
                using (var odr = cmd.ExecuteReader())
                {
                    if (odr.Read())
                    {
                        dbf.LibCache = Convert.ToDouble(odr[0]);
                    }
                    else
                    {
                        throw new Exception("无法监控到Lib缓存，可能系统繁忙，请稍后再试...");
                    }
                }
                //高速缓存行
                cmd.CommandText = "SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) ROW_CACHE FROM V$ROWCACHE";
                using (var odr = cmd.ExecuteReader())
                {
                    if (odr.Read())
                    {
                        dbf.RowCache = Convert.ToDouble(odr[1]);
                    }
                    else
                    {
                        throw new Exception("无法监控到高速缓存行数据，可能系统繁忙，请稍后再试...");
                    }
                }
                cmd.CommandText = "SELECT SUM(PINS) EXECUTIONS, SUM(RELOADS) CACHE_MISSES_WHILE_EXECUTING FROM V$LIBRARYCACHE";
                using (var odr = cmd.ExecuteReader())
                {
                    if (odr.Read())
                    {
                        dbf.Executions = Convert.ToInt32(odr[2]);
                        dbf.CMWE = Convert.ToInt32(odr[3]);
                    }
                    else
                    {
                        throw new Exception("无法监控字典缓冲区，可能系统繁忙，请稍后再试...");
                    }
                }
                return dbf;
            }
        }

        /// <summary>
        /// GetDBuffer(监控字典缓冲区)的返回值
        /// </summary>
        [Serializable]
        public class DBuffer
        {
            //Lib缓存
            public double LibCache { get; set; }
            //高速缓存行
            public double RowCache { get; set; }
            //处决
            public int Executions { get; set; }
            //缓存未命中，同时执行
            public int CMWE { get; set; }
        }

        /// <summary>
        /// 监控MTS(Monitoring MTS)
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <returns>【共享服务器忙碌状态、等待调度指数、总数、服务器指数】 DBuffer </returns>
        [WebMethod]
        public MTS GetMTS(string connectionString)
        {
            MTS mts = new MTS();
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //shared servers busy = 共享服务器忙碌状态
                cmd.CommandText = "select busy/(busy+idle) shared_servers_busy from v$dispatcher";
                using (var odr = cmd.ExecuteReader())
                {
                    if (odr.Read())
                    {
                        mts.SharedServersBusy = Convert.ToDouble(odr[0]);
                    }
                    else
                    {
                        throw new Exception("无法监控共享服务器忙碌状态，可能系统繁忙，请稍后再试...");
                    }
                }
                //dispatcher waits = 等待调度指数
                cmd.CommandText = "select sum(wait)/sum(totalq) dispatcher_waits from v$queue where type='dispatcher'";
                using (var odr = cmd.ExecuteReader())
                {
                    if (odr.Read())
                    {
                        mts.DispatcherWaits = Convert.ToDouble(odr[0]);
                    }
                    else
                    {
                        throw new Exception("无法监控到等待调度指数，可能系统繁忙，请稍后再试...");
                    }
                }
                //count(*) = 总数
                cmd.CommandText = "select count(*) from v$dispatcher";
                using (var odr = cmd.ExecuteReader())
                {
                    if (odr.Read())
                    {
                        mts.Count = Convert.ToInt32(odr[0]);
                    }
                    else
                    {
                        throw new Exception("无法监控到总数，可能系统繁忙，请稍后再试...");
                    }
                }
                //servers_highwater = 服务器指数
                // ......  
                //   Unfinished
                // ......
                cmd.CommandText = "select servers_highwater from v$mts";
                using (var odr = cmd.ExecuteReader())
                {
                    if (odr.Read())
                    {
                        mts.ServersHighwater = Convert.ToInt32(odr[0]);
                    }
                    else
                    {
                        throw new Exception("无法监控到服务器指数，可能系统繁忙，请稍后再试...");
                    }
                }
                return mts;
            }
        }

        /// <summary>
        /// GetMTS(监控MTS)的返回值
        /// </summary>
        [Serializable]
        public class MTS
        {
            //共享服务器忙碌状态
            public double SharedServersBusy { get; set; }
            //等待调度指数
            public double DispatcherWaits { get; set; }
            //总数
            public int Count { get; set; }
            //服务器指数
            public int ServersHighwater { get; set; }
        }

        /// <summary>
        /// 表的存储情况检查(Tables stored in the check)
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <param name="tablespaceName">表名（传入一个你要查询的表名）</param>
        /// <returns>【segment_name  sum(bytes) ,ext_quan】 TableCheck[] </returns>
        [WebMethod]
        public TableCheck[] GetTableCheck(string connectionString, string tablespaceName)
        {
            List<TableCheck> list = new List<TableCheck>();
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //segment_name  sum(bytes) ,ext_quan
                cmd.CommandText = "select segment_name,sum(bytes),count(*) ext_quan from dba_extents where tablespace_name='" + tablespaceName + "' and segment_type='TABLE' group by tablespace_name,segment_name";
                using (var odr = cmd.ExecuteReader())
                {
                    while (odr.Read())
                    {
                        TableCheck tc = new TableCheck()
                        {
                            SegmentName = Convert.ToString(odr[0]),
                            SumBytes = Convert.ToInt32(odr[1]),
                            ExtQuan = Convert.ToInt32(odr[2])
                        };
                    }
                    if (list.Count != 0)
                    {
                        return list.ToArray();
                    }
                    else
                    {
                        throw new Exception("无法监控表的存储情况检查，可能系统繁忙，请稍后再试...");
                    }
                }
            }
            return list.ToArray();
        }


        /// <summary>
        /// GetTableCheck(表的存储情况检查)的返回值
        /// </summary>
        [Serializable]
        public class TableCheck
        {
            //分部名称
            public string SegmentName { get; set; }
            //总字节
            public int SumBytes { get; set; }
            //
            public int ExtQuan { get; set; }
        }

        /// <summary>
        /// 索引的存储情况检查(Index stored in the check)
        /// </summary>
        /// <param name="connectionString">数据库连接（"DataSource=服务器;user=登录名;password=密码;"）</param>
        /// <param name="tablespaceName">索引（传入一个你要查询的索引）</param>
        /// <returns>【segment_name  count(*)】 IndexsCheck[] </returns>
        [WebMethod]
        public IndexsCheck[] GetIndexsCheck(string connectionString, string indexName)
        {
            List<IndexsCheck> list = new List<IndexsCheck>();
            using (var conn = new OracleConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                //segment_name  count(*)
                cmd.CommandText = "select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner=' " + indexName + "' group by segment_name";
                using (var odr = cmd.ExecuteReader())
                {
                    while (odr.Read())
                    {
                        IndexsCheck tc = new IndexsCheck()
                        {
                            SegmentName = Convert.ToString(odr[0]),
                            Counts = Convert.ToInt32(odr[1])
                        };
                    }
                    if (list.Count != 0)
                    {
                        return list.ToArray();
                    }
                    else
                    {
                        throw new Exception("无法监控到索引的存储情况检查，可能系统繁忙，请稍后再试...");
                    }
                }
            }
            return list.ToArray();
        }


        /// <summary>
        /// GetIndexsCheck(表的存储情况检查)的返回值
        /// </summary>
        [Serializable]
        public class IndexsCheck
        {
            //分部名称
            public string SegmentName { get; set; }
            //
            public int Counts { get; set; }
        }
    }
}
